import openpyxl
from django.db.models import F
from openpyxl.styles import Font, Alignment
from openpyxl.utils import get_column_letter

from apps.survey_system.survey.models import SurveyQuestionCategory, SurveyQuestion, SurveyAnswer, SurveyAccount, \
    PersonSurveyCategoryResults

header_font = Font(size=14, name=u'微软雅黑')
alignment = Alignment(
    horizontal='center',  # 水平对齐方式
    vertical='center',  # 垂直对齐方式
    wrap_text=True,
)

def personal_category_analysis(survey_id):
    """ 个人的问卷的每个维度分析结果 """
    cate_objs = SurveyQuestionCategory.objects.filter(survey_id=survey_id).values()  # 获取每个维度
    cate_ids = [i['id'] for i in cate_objs]  # 维度对象化
    question_qs = SurveyQuestion.objects.filter(survey_qs_cate_id__in=cate_ids) \
        .values('id', 'score', 'survey_qs_cate_id')  # 获取问题分数和所属维度
    cate_children = {i['id']: [] for i in cate_objs}  # 分类对象

    # 获取有填写的人的id
    survey_account_qs = SurveyAccount.objects.filter(survey_id=survey_id, status__in=[1, 2]).values_list('id', flat=True)
    account_children = {i: [] for i in survey_account_qs}

    PersonSurveyCategoryResults.objects.filter(survey_account_id__in=survey_account_qs).delete()  # 先删除之前的分析结果

    # 获取他们填写的答案
    question_answer_qs = SurveyAnswer.objects.filter(survey_account_id__in=survey_account_qs) \
        .values('question_id', 'score', 'survey_account_id')

    for i in question_answer_qs:  # 得到每一个人的答案
        account_children[i['survey_account_id']].append(i)

    for i in question_qs:
        cate_children[i['survey_qs_cate_id']].append(i)

    for i in cate_objs:  # 计算每个维度的总分
        cate_question = cate_children[i['id']]
        score = 0
        children = []
        for j in cate_question:
            score = score + j['score']
            children.append(j['id'])
        i['score'] = score
        i['children'] = children

    bluk_create_data = []
    for survey_acount in survey_account_qs:
        for cate in cate_objs:
            account_answer = account_children[survey_acount]
            sum_score = 0
            question_count = 0
            for answer in account_answer:
                if answer['question_id'] in cate['children']:
                    sum_score = sum_score + answer['score']
                    question_count = question_count + 1
            avg_score = round(sum_score / question_count, 1)
            analysis_item = {
                'survey_account_id': survey_acount,
                'survey_qs_cate_id': cate['id'],
                'result': {
                    'score': cate['score'],
                    'question_count': question_count,
                    'sum_score': sum_score,
                    'avg_score': avg_score,
                }
            }
            bluk_create_data.append(PersonSurveyCategoryResults(**analysis_item))
    PersonSurveyCategoryResults.objects.bulk_create(bluk_create_data, batch_size=5000, ignore_conflicts=True)


def download_category_analysis(category_qs, survey_account, survey_name):
    wb = openpyxl.Workbook()  # 创建一个excel
    ws = wb.active
    ws.title = f'{survey_name}的维度分析结果'

    table_header = ['学工号', '姓名', '性别', '所属年级', '学院', '专业', '班级', '提交时间', '所用时间']
    filter_value = ['username', 'realname', 'sex', 'grade_name', 'faculty_title', 'major_title', 'class_title',
                    'last_submit_time', 'use_time']
    for cate in category_qs:
        cate_name = cate['category_name']
        table_header.append(cate_name + '(总分)')
        table_header.append(cate_name + '(问题个数)')
        table_header.append(cate_name + '(总得分)')
        table_header.append(cate_name + '(平均分)')

    ws.append(table_header)  # 写入第一行数据
    # 对表头进行样式处理
    for row in ws[f"A1:{get_column_letter(ws.max_column)}1"]:
        for cell in row:
            cell.font = header_font
            cell.alignment = alignment

    # 写入数据
    for i in survey_account:
        row_data = []
        for key in filter_value:
            tmp = i.get(key, '')
            if tmp:
                tmp = str(tmp).replace('\x00', '')  # 将可能存在的隐藏字符替换为空字符串
            row_data.append(tmp)
        if i['cate_result']:
            for cate in category_qs:
                row_data.append(i['cate_result'][cate['id']]['result']['score'])
                row_data.append(i['cate_result'][cate['id']]['result']['question_count'])
                row_data.append(i['cate_result'][cate['id']]['result']['sum_score'])
                row_data.append(i['cate_result'][cate['id']]['result']['avg_score'])
        try:
            ws.append(row_data)
        except Exception as e:
            continue

    # 测试的时候保存在本地，上线时得删除
    # wb.save(f'./fixtures/survey_system/{survey_name}的维度分析结果.xlsx')
    return wb


def download_question_answer(category_qs, account_score):
    wb = openpyxl.Workbook()  # 默认创建一个名为'Sheet'的 sheet
    table_header = ['学工号', '姓名', '性别', '所属年级', '学院', '专业', '班级', '提交时间', '所用时间', '维度得分', '总分']
    filter_value = ['username', 'realname', 'sex', 'grade_name', 'faculty_title', 'major_title', 'class_title',
                    'last_submit_time', 'use_time', 'cate_score']
    for cate in category_qs:
        ws = wb.create_sheet(cate['category_name'])  # 创建sheet
        table_header1 = table_header.copy()  # 浅复制，不会改变原来列表
        for question in cate['questions']:  # 动态创建表头，将该维度的问题生成表头
            question_name = question['name']
            table_header1.append(question_name + '(答案)')
        ws.append(table_header1)  # 写入第一行数据
        # 对表头进行样式处理
        for row in ws[f"A1:{get_column_letter(ws.max_column)}1"]:
            for cell in row:
                cell.font = header_font
                cell.alignment = alignment

        # 写入数据
        for i in cate['children']:
            row_data = []
            for key in filter_value:  # 将个人信息写入
                tmp = i.get(key, '')
                if tmp:
                    tmp = str(tmp).replace('\x00', '')  # 将可能存在的隐藏字符替换为空字符串
                row_data.append(tmp)
            row_data.append(account_score.get(i['id'], 0))
            if i['children']:  # 将问题的答案写入
               for question in cate['questions']:
                   row_data.append(i['children'].get(question['id'], ''))
            try:
                ws.append(row_data)
            except Exception as e:
                continue

    ws = wb['Sheet']  # 删除第一个空的sheet
    wb.remove(ws)
    # 测试的时候保存在本地，上线时得删除
    # wb.save(f'./fixtures/survey_system/问卷填写答案.xlsx')
    return wb


def download_question_score(survey_account, question_qs, survey_name):
    wb = openpyxl.Workbook()  # 创建一个excel
    ws = wb.active
    ws.title = f'{survey_name}的问题得分'

    table_header = ['学工号', '姓名', '性别', '所属年级', '学院', '专业', '班级', '提交时间', '所用时间', '总分']
    filter_value = ['username', 'realname', 'sex', 'grade_name', 'faculty_title', 'major_title', 'class_title',
                    'last_submit_time', 'use_time', 'all_score']
    for question in question_qs:
        table_header.append(question['name'])

    ws.append(table_header)  # 写入第一行数据
    # 对表头进行样式处理
    for row in ws[f"A1:{get_column_letter(ws.max_column)}1"]:
        for cell in row:
            cell.font = header_font
            cell.alignment = alignment

    # 写入数据
    for i in survey_account:
        row_data = []
        for key in filter_value:
            tmp = i.get(key, '')
            if tmp:
                tmp = str(tmp).replace('\x00', '')  # 将可能存在的隐藏字符替换为空字符串
            row_data.append(tmp)
        if i['children']:  # 将问题的答案写入
            for question in question_qs:
                row_data.append(i['children'].get(question['id'], ''))
        try:
            ws.append(row_data)
        except Exception as e:
            continue

    return wb


def person_score_analysis(survey_id, survey_account):
    cate_objs = SurveyQuestionCategory.objects.filter(survey_id=survey_id).values()
    cate_ids = []
    cate_children = {}
    cate_questions_id = {}
    question_ids = []
    for i in cate_objs:
        cate_ids.append(i['id'])
        cate_children[i['id']] = []
        cate_questions_id[i['id']] = []

    # cate_ids = [i['id'] for i in cate_objs]
    question_qs = SurveyQuestion.objects.filter(survey_qs_cate_id__in=cate_ids) \
        .values('id', 'score', 'survey_qs_cate_id')
    # cate_children = {i['id']: [] for i in cate_objs}
    question_answer_qs = SurveyAnswer.objects.filter(survey_account_id=survey_account) \
        .values('question_id', 'score', class_title=F('survey_account__class_title'))
    question_answer_ls = list(question_answer_qs)
    question_answer_dict = {answer['question_id']: answer for answer in question_answer_ls}

    for i in question_qs:
        i['got_score'] = question_answer_dict[i['id']]['score']
        cate_children[i['survey_qs_cate_id']].append(i)
        cate_questions_id[i['survey_qs_cate_id']].append(i['id'])
        question_ids.append(i['id'])

    answer_qs = SurveyAnswer.objects.filter(question_id__in=question_ids) \
        .values('question_id', 'score', class_title=F('survey_account__class_title'))

    survey_account_qs = SurveyAccount.objects.filter(survey_id=survey_id, status__in=[1, 2]).values('class_title', 'id')
    class_account = 0
    all_account = len(survey_account_qs)
    if question_answer_ls[0]['class_title']:
        for i in survey_account_qs:
            if i['class_title'] == question_answer_ls[0]['class_title']:
                class_account += 1

    res_data = [
        ['维度名', '个人平均分', '班级平均分', '总平均分'],
    ]

    for i in cate_objs:
        class_all_score = 0
        all_score = 0

        account_answer = cate_children[i['id']]
        score = 0
        got_score = 0
        question_count = 0

        for k in answer_qs:
            if k['question_id'] in cate_questions_id[i['id']]:
                all_score += k['score']
                if question_answer_ls[0]['class_title'] and question_answer_ls[0]['class_title'] == k['class_title']:
                    class_all_score += k['score']

        for j in account_answer:
            question_count = question_count + 1
            score = score + j['score']
            got_score = got_score + j['got_score']

        person_avg_score = round(got_score / question_count, 1)

        if class_account == 0:
            class_avg_score = 0
        else:
            class_avg_score = round(class_all_score / (question_count * class_account), 1)

        all_avg_score = round(all_score / (question_count * all_account), 1)

        data = [i['category_name'], person_avg_score, class_avg_score, all_avg_score]
        res_data.append(data)
    return res_data
